package com.API.dal.providers;

import com.API.dal.entities.Costs;
import com.API.dal.entities.CostsTypes;
import com.API.dal.entities.Equipments;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Scope;
import org.springframework.context.annotation.ScopedProxyMode;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;

import java.util.List;

@Repository
@Scope(value = "request", proxyMode = ScopedProxyMode.INTERFACES)
public class CostsProviderImp implements CostsProvider{

    @Autowired
    SessionFactory sessionFactory;

    @Override
    @Transactional(propagation= Propagation.REQUIRED, rollbackFor=Exception.class)
    public void addCost(Costs cost) {
        Session session = sessionFactory.getCurrentSession();
        session.save(cost);
    }

    @Override
    @Transactional(propagation=Propagation.REQUIRED, rollbackFor=Exception.class)
    public void updateCost(Costs cost) {
        Session session = sessionFactory.getCurrentSession();
        session.update(cost);
    }

    @Override
    @Transactional(propagation=Propagation.REQUIRED, rollbackFor=Exception.class)
    public List<CostsTypes> getCostsTypes() {
        List<CostsTypes> costsTypesList;
        Session session = sessionFactory.getCurrentSession();
        Query query = session.createQuery("from CostsTypes ct");
        costsTypesList = query.list();
        return costsTypesList;
    }

    @Override
    @Transactional(propagation=Propagation.REQUIRED, rollbackFor=Exception.class)
    public CostsTypes getCostTypeByName(String name) {
        CostsTypes costType;
        Session session = sessionFactory.getCurrentSession();
        Query query = session.createQuery("from CostsTypes ct where ct.name = '" + name + "'");
        costType = (CostsTypes) query.uniqueResult();
        return costType;
    }

    @Override
    @Transactional(propagation=Propagation.REQUIRED, rollbackFor=Exception.class)
    public CostsTypes getCostsTypesById(int costType_id) {
        CostsTypes costType;
        Session session = sessionFactory.getCurrentSession();
        Query query = session.createQuery("from CostsTypes ct where ct.id = " + costType_id);
        costType = (CostsTypes) query.uniqueResult();
        return costType;
    }

    @Override
    @Transactional(readOnly=true,propagation= Propagation.SUPPORTS)
    public List<Costs> getEmployeesCosts(int project_id) {
        List<Costs> costsList;
        Session session = sessionFactory.getCurrentSession();
        Query query = session.createQuery("select co from Costs co, EmployeesCosts ec, EmployeeProject ep " +
                "where co.id = ec.costs.id and ec.employees.id = ep.employees.id and " +
                    " ep.objects.id = " + project_id);
        costsList = query.list();
        return costsList;
    }

    @Override
    @Transactional(readOnly=true,propagation= Propagation.SUPPORTS)
    public List<Costs> getCosts() {
        List<Costs> costsList;
        Session session = sessionFactory.getCurrentSession();
        Query query = session.createQuery("from Costs co");
        costsList = query.list();
        return costsList;
    }

    @Override
    @Transactional(readOnly=true,propagation= Propagation.SUPPORTS)
    public List<Costs> getProjectCosts(int project_id) {
        List<Costs> costsList;
        Session session = sessionFactory.getCurrentSession();
        Query query = session.createQuery("select  co from Costs co, EmployeesCosts ec,EmployeeProject ep " +
                " where co.id = ec.costs.id and ec.employees.id = ep.employees.id and " +
                "ep.objects.id = " + project_id);
        costsList = query.list();
        query = session.createQuery("select  co from Costs co, EquipmentCosts ec,EquipmentsProjects ep " +
                " where co.id = ec.costs.id and ec.equipments.id = ep.equipments.id and " +
                "ep.objects.id = " + project_id);
        costsList.addAll(query.list());
        return costsList;
    }

    @Override
    @Transactional(readOnly=true,propagation= Propagation.SUPPORTS)
    public List<Costs> getProjectCosts(String project_name) {
        List<Costs> costsList;
        Session session = sessionFactory.getCurrentSession();
        Query query = session.createQuery("select  co from Costs co, EmployeesCosts ec,EmployeeProject ep " +
                " where co.id = ec.costs.id and ec.employees.id = ep.employees.id and " +
                "ep.objects.name = '" + project_name + "'");
        costsList = query.list();
        query = session.createQuery("select  co from Costs co, EquipmentCosts ec,EquipmentsProjects ep " +
                " where co.id = ec.costs.id and ec.equipments.id = ep.equipments.id and " +
                "ep.objects.name = '" + project_name + "'");
        costsList.addAll(query.list());
        return costsList;
    }


    @Override
    @Transactional(readOnly=true,propagation= Propagation.SUPPORTS)
    public List<Costs> getEquipmentCosts(int equipment_id) {
        List<Costs> costsList;
        Session session = sessionFactory.getCurrentSession();
        Query query = session.createQuery("select co from Costs co, EquipmentCosts eqc " +
                "where co.id = eqc.costs.id and eqc.equipments.id =  " + equipment_id);
        costsList = query.list();
        return costsList;
    }

    @Override
    @Transactional(readOnly=true,propagation= Propagation.SUPPORTS)
    public List<Costs> getEquipmentCosts(String equipment_name) {
        List<Costs> costsList;
        Session session = sessionFactory.getCurrentSession();
        Query query = session.createQuery("select co from Costs co, EquipmentCosts eqc " +
                "where co.id = eqc.costs.id and eqc.equipments.name =  " + equipment_name);
        costsList = query.list();
        return costsList;
    }



    @Override
    @Transactional(readOnly=true,propagation= Propagation.SUPPORTS)
    public List<Costs> getEmployeeCosts(String employee_name) {
        List<Costs> costsList;
        Session session = sessionFactory.getCurrentSession();
        Query query = session.createQuery("from Costs co " +
                "where co.id in (select ec.costs.id from EmployeesCosts ec where ec.employees.name = '" + employee_name + "' )");
        costsList = query.list();
        return costsList;
    }

    @Override
    @Transactional(readOnly=true,propagation= Propagation.SUPPORTS)
    public List<Costs> getEmployeeCosts(int employee_id) {
        List<Costs> costsList;
        Session session = sessionFactory.getCurrentSession();
        Query query = session.createQuery("from Costs co " +
                "where co.id in (select ec.costs.id from EmployeesCosts ec where ec.employees.id = " + employee_id + " )");
        costsList = query.list();
        return costsList;
    }
}
